<!doctype html>
<html>
<head>
    <title></title>
    <meta charset="utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"/>

    
    <link href="http://demo.gcpowertools.com.cn/SpreadJS/TutorialSample/external/spreadjs/css/gcspread.sheets.8.40.20151.0.css" rel="stylesheet" type="text/css"/>

    <script src="http://demo.gcpowertools.com.cn/SpreadJS/TutorialSample/external/external/jquery-1.8.2.min.js" type="text/javascript"></script>

    <script type="text/javascript" src="http://demo.gcpowertools.com.cn/SpreadJS/TutorialSample/external/spreadjs/gcspread.sheets.all.8.40.20151.0.min.js"></script>
    

    

    
    
    

    <style type="text/css">
        .demo-options, .option-row { 
            margin-top: 10px; 
        }
    
    </style>
    <script type="text/javascript">
    
        var spreadNS = GcSpread.Sheets;

        $(document).ready(function () {
            var spread = new GcSpread.Sheets.Spread(document.getElementById("ss"), { sheetCount: 1 });
            initSpread(spread);
        });

        function initSpread(spread) {
            var sheet = spread.getSheet(0);

            sheet.isPaintSuspended(true);
            var cfs = sheet.getConditionalFormats();

            var table = sheet.addTable("tblOperatingExpenses2", 4, 1, 11, 6, new spreadNS.TableStyle());
            table.showFooter(true);

            var dataTable = [];
            dataTable[0] = new Array("STATUS", "OPERATING", "BUDGET", "ACTUAL", "DIFFERENCE ($)", "DIFFERENCE (%)");
            dataTable[1] = new Array(0, "Advertising", 600, 545, 0, 0);
            dataTable[2] = new Array(0, "Debts", 125, 150, 0, 0);
            dataTable[3] = new Array(0, "Benefits", 100, 100, 0, 0);
            dataTable[4] = new Array(0, "Supplies", 100, 90, 0, 0);
            dataTable[5] = new Array(0, "Postage", 150, 145, 0, 0);
            dataTable[6] = new Array(0, "Rent or mortgage", 1000, 1000, 0, 0);
            dataTable[7] = new Array(0, "Sales expenses", 500, 630, 0, 0);
            dataTable[8] = new Array(0, "Taxes", 350, 375, 0, 0);
            dataTable[9] = new Array(0, "Utilities", 400, 370, 0, 0);
            dataTable[10] = new Array(0, "Other", 500, 435, 0, 0);
            dataTable[11] = new Array(0, "Total Expenses", 0, 0, 0, 0);

            sheet.addSpan(0, 1, 1, 4);
            sheet.addSpan(0, 5, 1, 2);
            sheet.addSpan(1, 1, 1, 6);
            sheet.addSpan(2, 1, 1, 6);
            sheet.addSpan(3, 1, 1, 2);
            sheet.addSpan(16, 1, 1, 6);
            sheet.addSpan(17, 1, 1, 6);

            sheet.getDefaultStyle().vAlign = spreadNS.VerticalAlign.center;
            sheet.getDefaultStyle().font = "lighter 10pt Calibri";
            sheet.getDefaultStyle().foreColor = "rgb(68, 84, 106)";
            sheet.gridline = new spreadNS.LineBorder("Black", spreadNS.LineStyle.empty);
            sheet.setGridlineOptions({ showVerticalGridline: false, showHorizontalGridline: false });
            sheet.getCell(0, 1).value("Expense Budget").font("lighter 28pt Calibri");
            sheet.getCell(0, 5).value("CONTOSO, 2013").font("11pt Calibri").foreColor("rgb(64, 64, 64)")
                .hAlign(spreadNS.HorizontalAlign.right).vAlign(spreadNS.VerticalAlign.bottom);
            sheet.getCell(3, 1).value("OPERATING BUDGET").font("bold 13pt Calibri ").foreColor("rgb(64, 64, 64)");
            sheet.getCells(4, 1, 4, 6).font("bold 10pt Calibri")
                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
            sheet.getCells(15, 1, 15, 6).font("bold 10pt Calibri")
                .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
            sheet.getCells(3, 1, 3, 2)
                .borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))
                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin))
                .borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
            sheet.getCells(4, 1, 15, 1).borderLeft(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin)).hAlign(spreadNS.HorizontalAlign.center);
            sheet.getCells(4, 6, 15, 6).borderRight(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));
            sheet.getCells(5, 1, 14, 6).borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.dashed));;

            for (var row = 4; row < 16; row++) {
                for (var col = 1; col < 7; col++) {
                    sheet.setValue(row, col, dataTable[row - 4][col - 1]);
                }
            }

            var rowHeights = new Array(74, 4, 20);
            for (var row = 0; row < 3; row++) {
                sheet.setRowHeight(row, rowHeights[row]);
            }
            for (var row = 3; row < 17; row++) {
                sheet.setRowHeight(row, 24);
            }
            sheet.setRowHeight(17, 4);

            var colWidths = new Array(18, 83, 111, 84, 85, 126, 129);
            for (var col = 0; col < 7; col++) {
                sheet.setColumnWidth(col, colWidths[col]);
            }

            sheet.getCells(1, 1, 1, 6)
                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))
                .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));

            sheet.getCells(17, 1, 17, 6)
                .borderTop(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thick))
                .borderBottom(new spreadNS.LineBorder("rgb(217,217,217)", spreadNS.LineStyle.thin));

            sheet.getCells(3, 3, 15, 5).formatter('"$"#,##0.00');
            sheet.getCells(3, 6, 14, 6).formatter('0%');
            sheet.getCell(15, 6).formatter('0.0%');

            var iconSetRule1 = new spreadNS.IconSetRule();
            iconSetRule1.ranges = [new spreadNS.Range(15, 1, 1, 1)];
            iconSetRule1.iconSetType(spreadNS.IconSetType.ThreeSymbolsCircled)
                .showIconOnly(true);
            var icons = iconSetRule1.iconCriteria();
            icons[0] = new spreadNS.IconCriterion(false, spreadNS.IconValueType.Number, -1);
            icons[1] = new spreadNS.IconCriterion(true, spreadNS.IconValueType.Number, 1);

            var cellValueRule1 = new spreadNS.CellValueRule(spreadNS.ComparisonOperator.LessThan, 0);
            cellValueRule1.style = new spreadNS.Style();
            cellValueRule1.style.foreColor = "red";
            cellValueRule1.ranges = [new spreadNS.Range(15, 5, 1, 2)];


            var dataBarRule1 = new spreadNS.DataBarRule();
            dataBarRule1.minimumType(spreadNS.ScaleValueType.Automin);
            dataBarRule1.maximumType(spreadNS.ScaleValueType.Automax);
            dataBarRule1.ranges = [new spreadNS.Range(5, 4, 10, 1)];
            dataBarRule1.color("orange").showBorder(true).borderColor("orange").dataBarDirection(spreadNS.BarDirection.RightToLeft);


            var dataBarRule2 = new spreadNS.DataBarRule();
            dataBarRule2.minimumType(spreadNS.ScaleValueType.Automin);
            dataBarRule2.maximumType(spreadNS.ScaleValueType.Automax);
            dataBarRule2.ranges = [new spreadNS.Range(5, 3, 10, 1)];
            dataBarRule2.color("rgb(0,138,239)").showBorder(true).borderColor("rgb(0,138,239)");


            var dataBarRule3 = new spreadNS.DataBarRule();
            dataBarRule3.minimumType(spreadNS.ScaleValueType.LowestValue);
            dataBarRule3.maximumType(spreadNS.ScaleValueType.HighestValue);
            dataBarRule3.ranges = [new spreadNS.Range(5, 6, 10, 1)];
            dataBarRule3.color("rgb(91,155,213)").showBorder(true).borderColor("rgb(91,155,213)")
                .negativeFillColor("rgb(237,125,49)").useNegativeBorderColor(true).negativeBorderColor("rgb(237,125,49)")
                .axisPosition(spreadNS.DataBarAxisPosition.CellMidPoint);

            var iconSetRule2 = new spreadNS.IconSetRule();
            iconSetRule2.ranges = [new spreadNS.Range(5, 1, 10, 1)];
            iconSetRule2.iconSetType(spreadNS.IconSetType.ThreeSymbolsUncircled)
                .reverseIconOrder(true)
                .showIconOnly(true);

            cfs.addRule(iconSetRule1);
            cfs.addRule(cellValueRule1);
            cfs.addRule(dataBarRule1);
            cfs.addRule(dataBarRule2);
            cfs.addRule(dataBarRule3);
            cfs.addRule(iconSetRule2);

            table.setColumnFormula(0, "=tblOperatingExpenses2[[#Totals],[DIFFERENCE (%)]]");
            table.setColumnFormula(2, "=SUBTOTAL(109,[BUDGET])");
            table.setColumnFormula(3, "=SUBTOTAL(109,[ACTUAL])");
            table.setColumnFormula(4, "=SUBTOTAL(109,[DIFFERENCE ($)])");
            table.setColumnFormula(5, '=IFERROR(SUM(tblOperatingExpenses2[[#Totals],[DIFFERENCE ($)]]/tblOperatingExpenses2[[#Totals],[BUDGET]]),"")');

            table.setColumnDataFormula(0, '=IFERROR([@ACTUAL]/[@BUDGET],"")');
            table.setColumnDataFormula(4, '=[@BUDGET]-[@ACTUAL]');
            table.setColumnDataFormula(5, '=IFERROR([@[DIFFERENCE ($)]]/[@BUDGET],"")');

            sheet.isPaintSuspended(false);
        }
        
    </script>
</head>
<body>
    <div class="sample-turtorial">
        <div id="ss" style="width:100%; height:620px;border: 1px solid gray;"></div>
    </div></body>
</html>
